package org.gdstash.db;

import org.gdstash.util.GDMsgFormatter;
import org.gdstash.util.GDMsgLogger;

import java.sql.*;
import java.util.LinkedList;
import java.util.List;

public class DBSkillTreeAlloc {
    public static final String TABLE_NAME = "GD_SKILLTREE_ALLOC";
    private static final int ROW_SKILLTREE_ID = 1;
    private static final int ROW_SKILL_ID = 2;
    private static final int ROW_INDEX = 3;
    private String treeID;
    private String skillID;
    private int index;

    public DBSkillTreeAlloc() {
        this.treeID = null;
        this.skillID = null;
        this.index = 0;
    }

    public DBSkillTreeAlloc(final String skillID, final int index) {
        this.treeID = null;
        this.skillID = skillID;
        this.index = index;
    }

    public String getSkillID() {
        return this.skillID;
    }

    public String getTreeID() {
        return this.treeID;
    }

    public static void createTable(final Connection conn) throws SQLException {
        final String dropTable = "DROP TABLE GD_SKILLTREE_ALLOC";
        final String createTable = "CREATE TABLE GD_SKILLTREE_ALLOC (SKILLTREE_ID       VARCHAR(256) NOT NULL, SKILL_ID           VARCHAR(256) NOT NULL, INDEX              INTEGER NOT NULL, PRIMARY KEY (SKILLTREE_ID, SKILL_ID, INDEX))";
        try (final Statement st = conn.createStatement()) {
            if (GDDBUtil.tableExists(conn, "GD_SKILLTREE_ALLOC")) {
                st.execute(dropTable);
            }
            st.execute(createTable);
            st.close();
            conn.commit();
        } catch (SQLException ex) {
            throw ex;
        }
    }

    public static void delete(final Connection conn, final String skillTreeID) throws SQLException {
        final String deleteEntry = "DELETE FROM GD_SKILLTREE_ALLOC WHERE SKILLTREE_ID = ?";
        try (final PreparedStatement ps = conn.prepareStatement(deleteEntry)) {
            ps.setString(1, skillTreeID);
            ps.executeUpdate();
            ps.close();
        } catch (SQLException ex) {
            throw ex;
        }
    }

    public static void insert(final Connection conn, final DBSkillTree dbSkillTree) throws SQLException {
        if (dbSkillTree.getSkillIDList() == null) {
            return;
        }
        if (dbSkillTree.getSkillIDList().isEmpty()) {
            return;
        }
        final String insert = "INSERT INTO GD_SKILLTREE_ALLOC VALUES (?,?,?)";
        try (final PreparedStatement ps = conn.prepareStatement(insert)) {
            for (final DBSkillTreeAlloc alloc : dbSkillTree.getSkillAllocList()) {
                ps.setString(1, dbSkillTree.getSkillTreeID());
                ps.setString(2, alloc.skillID);
                ps.setInt(3, alloc.index);
                ps.executeUpdate();
                ps.clearParameters();
            }
            ps.close();
            conn.commit();
        } catch (SQLException ex) {
            throw ex;
        }
    }

    public static List<DBSkillTreeAlloc> getAllocByTreeID(final String skillTreeID) {
        List<DBSkillTreeAlloc> skillIDs = null;
        final String command = "SELECT * FROM GD_SKILLTREE_ALLOC WHERE SKILLTREE_ID = ? ORDER BY INDEX";
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            ps.setString(1, skillTreeID);
            try (final ResultSet rs = ps.executeQuery()) {
                skillIDs = wrapAlloc(rs);
                conn.commit();
            } catch (SQLException ex) {
                throw ex;
            }
        } catch (SQLException ex2) {
            final Object[] args = {skillTreeID, "GD_SKILLTREE_ALLOC"};
            final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_READ_TABLE_BY_ID", args);
            GDMsgLogger.addError(msg);
            GDMsgLogger.addError(ex2);
        }
        return skillIDs;
    }

    public static List<DBSkillTreeAlloc> getAllocBySkillID(final String skillID) {
        List<DBSkillTreeAlloc> skillIDs = null;
        final String command = "SELECT * FROM GD_SKILLTREE_ALLOC WHERE SKILL_ID = ?";
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            ps.setString(1, skillID);
            try (final ResultSet rs = ps.executeQuery()) {
                skillIDs = wrapAlloc(rs);
                conn.commit();
            } catch (SQLException ex) {
                throw ex;
            }
        } catch (SQLException ex2) {
            final Object[] args = {skillID, "GD_SKILLTREE_ALLOC"};
            final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_READ_TABLE_BY_ID", args);
            GDMsgLogger.addError(msg);
            GDMsgLogger.addError(ex2);
        }
        return skillIDs;
    }

    public static List<String> getSkillIDByTreeID(final String skillTreeID) {
        List<String> skillIDs = null;
        final String command = "SELECT * FROM GD_SKILLTREE_ALLOC WHERE SKILLTREE_ID = ? ORDER BY INDEX";
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            ps.setString(1, skillTreeID);
            try (final ResultSet rs = ps.executeQuery()) {
                skillIDs = wrapSkill(rs);
                conn.commit();
            } catch (SQLException ex) {
                throw ex;
            }
        } catch (SQLException ex2) {
            final Object[] args = {skillTreeID, "GD_SKILLTREE_ALLOC"};
            final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_READ_TABLE_BY_ID", args);
            GDMsgLogger.addError(msg);
            GDMsgLogger.addError(ex2);
        }
        return skillIDs;
    }

    public static String getTreeIDBySkillID(final String skillID) {
        List<String> skillIDs = null;
        final String command = "SELECT * FROM GD_SKILLTREE_ALLOC, GDC_PLAYER_MASTERIES WHERE GD_SKILLTREE_ALLOC.SKILL_ID = ? AND GD_SKILLTREE_ALLOC.SKILLTREE_ID = GDC_PLAYER_MASTERIES.SKILLTREE_ID";
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            ps.setString(1, skillID);
            try (final ResultSet rs = ps.executeQuery()) {
                skillIDs = wrapTree(rs);
                conn.commit();
            } catch (SQLException ex) {
                throw ex;
            }
        } catch (SQLException ex2) {
            final Object[] args = {skillID, "GD_SKILLTREE_ALLOC"};
            final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_READ_TABLE_BY_ID", args);
            GDMsgLogger.addError(msg);
            GDMsgLogger.addError(ex2);
        }
        if (skillIDs == null) {
            return null;
        }
        if (skillIDs.isEmpty()) {
            return null;
        }
        return skillIDs.get(0);
    }

    private static List<String> wrapSkill(final ResultSet rs) throws SQLException {
        final LinkedList<String> list = new LinkedList<String>();
        while (rs.next()) {
            final String skillID = rs.getString(2);
            list.add(skillID);
        }
        return list;
    }

    private static List<String> wrapTree(final ResultSet rs) throws SQLException {
        final LinkedList<String> list = new LinkedList<String>();
        while (rs.next()) {
            final String treeID = rs.getString(1);
            list.add(treeID);
        }
        return list;
    }

    private static List<DBSkillTreeAlloc> wrapAlloc(final ResultSet rs) throws SQLException {
        final LinkedList<DBSkillTreeAlloc> list = new LinkedList<DBSkillTreeAlloc>();
        while (rs.next()) {
            final DBSkillTreeAlloc alloc = new DBSkillTreeAlloc();
            alloc.treeID = rs.getString(1);
            alloc.skillID = rs.getString(2);
            alloc.index = rs.getInt(3);
            list.add(alloc);
        }
        return list;
    }
}
